package com.starkuz.dao.impl;


import com.starkuz.dao.ifaces.leads.LaunDao;
import com.starkuz.model.Laun;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class LaunDaoImpl implements LaunDao {

    private static final String INSERT_LAUN = "insert into launs (siteId, email, dateMade) values (?, ?, ?)";
    private static final String SELECT_ALL_LAUNS = "select * from launs";
    private static final String SELECT_ALL_LAUNS_BY_SITE_ID = "select * from launs where siteId = ?";
    private static final String SELECT_ALL_LAUNS_BY_USER_ID = "join!!!";  //todo: реализовать джойн
    private static final String COUNT_ALL_LAUNS_BY_SITE_ID = "select count(*) value from launs where siteId = ?";
    private static final String COUNT_ALL_LAUNS_BY_USER_ID = "join!!!";   //todo: реализовать джойн

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public void addLaun(Integer siteId, String email) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con =  ds.getConnection();
            psql = con.prepareStatement(INSERT_LAUN);
            psql.setInt(1, siteId);
            psql.setString(2, email);
            psql.setDate(3, new Date(new java.util.Date().getTime()));
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<Laun> getAllLauns() {
        List<Laun> launs = null;
        Connection con = null;
        Statement st = null;
        ResultSet res = null;
        try {
            con = ds.getConnection();
            st = con.createStatement();
            res = st.executeQuery(SELECT_ALL_LAUNS);
            launs = resultSetToLaunsList(res);
            return launs;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                res.close();
                st.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return new ArrayList<Laun>();
    }


    @Override
    public List<Laun> getAllLaunsBySiteId(Integer siteId) {
        List<Laun> launs = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LAUNS_BY_SITE_ID);
            psql.setInt(1, siteId);
            launs = resultSetToLaunsList(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return launs;
    }

    @Override
    public List<Laun> getAllLaunsByUserId(Integer userId) {
        List<Laun> launs = null;
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_LAUNS_BY_USER_ID);
            psql.setInt(1, userId);
            launs = resultSetToLaunsList(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return launs;
    }

    @Override
    public Integer countLaunBySiteId(Integer siteId) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_LAUNS_BY_SITE_ID);
                psql.setInt(1, siteId);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }

    @Override
    public Integer countLaunByUserId(Integer siteId) {
        Integer value = null;
        Connection con = null;
        PreparedStatement psql = null;
        ResultSet rs = null;
        try {
            con = ds.getConnection();
            try {
                psql = con.prepareStatement(COUNT_ALL_LAUNS_BY_USER_ID);
                psql.setInt(1, siteId);
                rs = psql.executeQuery();
                while (rs.next()) {
                    value = rs.getInt("value");
                }
                rs.close();
            } catch (SQLException s) {
                s.printStackTrace();
            }
            con.close();
            rs.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return value;
    }

    private List<Laun> resultSetToLaunsList(ResultSet rs) throws SQLException {
        List<Laun> launs = new ArrayList<Laun>();
        while (rs.next()) {
            Laun laun = new Laun();
            laun.setId(rs.getInt("id"));
            laun.setSiteId(rs.getInt("siteId"));
            laun.setEmail(rs.getString("email"));
            laun.setDateMade(rs.getDate("dateMade"));

            launs.add(laun);
        }
        return launs;
    }
}
